#!/bin/bash

###########################################################################################################
## 代码执行顺序：hive_all_data.sql --> hive_create_table.sh --> export_table.sql --> mysql.sql --> sqoop.sh ##
###########################################################################################################

#list[1]=one
#list[2]=two
#list[3]=three
#list[4]=four
#list[5]=five
#list[6]=six
list=("" "one" "two" "three" "four" "five" "six")

begin=3
loop=6

rm -f ~/as.txt

for i in $(seq $begin $loop); do
  sql="
use productdescriptionanalysis_all;
set hive.exec.mode.local.auto = true;
set hive.exec.mode.local.auto.inputbytes.max = 5368709120;
set hive.exec.mode.local.auto.input.files.max = 20;
set hive.auto.convert.join=false;
set hive.exec.reducers.bytes.per.reducer = 5000000000;
set hive.strict.checks.cartesian.product = false;

drop table if exists ${list[i]}_words;
create table if not exists ${list[i]}_words
as
select words, count
from (
         select words, count(*) count
         from (
                  select substr_words(sentence, ${i}) words
                  from sentences
              ) ${list[i]}_words_tmp
         group by words
     ) tmp
where tmp.count > 50;
"

  filter=""
  for ((j = 0; j < i; j++)); do
    insert_overwrite="
insert overwrite table ${list[i]}_words select tmp.words, tmp.count
from (
         select words, count, split(words, \"\") split
         from ${list[i]}_words
     ) tmp
where tmp.split[${j}] not in (select words_lower.word from words_lower);
"
    filter=${filter}${insert_overwrite}
  done
  sql=${sql}${filter}

  echo $sql >> ~/as.txt

done
# hive -e "hql"  hql中含有--注释时，会报错：
# 好像转义"也会有问题，输出到文件中后，用-f执行就没有问题了，但是在idea中显示时，\"有Warning
# NoViableAltException(-1@[])
# FAILED: ParseException line cannot recognize input near

hive -f ~/as.txt